﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using ThanhTu.GNCMedia.KQXS.DataAccess.DO;
using ThanhTu.GNCMedia.KQXS.Common;

namespace ThanhTu.GNCMedia.KQXS.DataAccess.DAL
{
    public class ProvinceDAL : BaseDAL
    {
        public ProvinceDAL() : base() { }
        public int addProvince(ProvinceDO vProvinceDO)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "Province_PKG.SaveProvince";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
            oraParam.Value = vProvinceDO.ProvinceID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_REGION_ID", OracleDbType.Varchar2);
            oraParam.Value = vProvinceDO.RegionID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_PRINCE_NAME", OracleDbType.Varchar2);
            oraParam.Value = vProvinceDO.PrinceName;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_LOTTERY_NAME", OracleDbType.Varchar2);
            oraParam.Value = vProvinceDO.LotteryName;
            oraComm.Parameters.Add(oraParam);


            oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
            { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;
        }

        public int delete(string ID_Tinh)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "Province_PKG.DeleteProvince";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
            oraParam.Value = ID_Tinh;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
            { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;
        }

        public ProvinceDO getbyProvince(string ID_tinh)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "Province_PKG.GetByProvince";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
            oraParam.Value = ID_tinh;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataRow dr = null;
            ProvinceDO vProvinceDO = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        dr = ds.Tables[0].Rows[0];
                        vProvinceDO = new ProvinceDO();
                        if (!Convert.IsDBNull(dr["PROVINCEID"]))
                        { vProvinceDO.RegionID = Convert.ToString(dr["PROVINCEID"]); }

                        if (!Convert.IsDBNull(dr["REGIONID"]))
                        { vProvinceDO.RegionID = Convert.ToString(dr["REGIONID"]); }

                        if (!Convert.IsDBNull(dr["PRINCENAME"]))
                        { vProvinceDO.RegionID = Utils.ToUTF8String((byte[])dr["PRINCENAME"]) ; }


                        if (!Convert.IsDBNull(dr["LOTTERYNAME"]))
                        { vProvinceDO.RegionID = Utils.ToUTF8String((byte[])dr["LOTTERYNAME"]); }

                        dr = null;
                    }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return vProvinceDO;
        }

        public List<ProvinceDO> selectadd()
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "Province_PKG.GerAllProvinceNonePage";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;
            List<ProvinceDO> vProvinceDOCollection = null;
            DataRow dr = null;
            ProvinceDO vProvinceDO = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                    vProvinceDOCollection = new List<ProvinceDO>();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dr = dt.Rows[i];
                        vProvinceDO = new ProvinceDO();

                        if (!Convert.IsDBNull(dr["PROVINCEID"]))
                        { vProvinceDO.ProvinceID = Convert.ToString(dr["PROVINCEID"]); }

                        if (!Convert.IsDBNull(dr["REGIONID"]))
                        { vProvinceDO.RegionID = Convert.ToString(dr["REGIONID"]); }


                        if (!Convert.IsDBNull(dr["PRINCENAME"]))

                        { vProvinceDO.PrinceName = Utils.ToUTF8String((byte[])dr["PRINCENAME"]); }

                        if (!Convert.IsDBNull(dr["LOTTERYNAME"]))
                        { vProvinceDO.LotteryName = Utils.ToUTF8String((byte[])dr["LOTTERYNAME"]) ; }

                        vProvinceDOCollection.Add(vProvinceDO);
                        dr = null;
                        vProvinceDO = null;
                    }

                    dt.Dispose();
                    dt = null;
                }

                ds.Dispose();
                ds = null;
            }
            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return vProvinceDOCollection;
        }

        public List<ProvinceDO> selectadd_idvung(string id_vung)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "Province_PKG.GerAllProvinceNonePage_Region";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_REGION_ID", OracleDbType.Varchar2);
                oraParam.Value = id_vung;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;
                    List<ProvinceDO> vProvinceDOCollection = null;
                    DataRow dr = null;
                    ProvinceDO vProvinceDO = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                            vProvinceDOCollection = new List<ProvinceDO>();

                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                dr = dt.Rows[i];
                                vProvinceDO = new ProvinceDO();

                                if (!Convert.IsDBNull(dr["PROVINCEID"]))
                                { vProvinceDO.ProvinceID = Convert.ToString(dr["PROVINCEID"]); }

                                if (!Convert.IsDBNull(dr["REGIONID"]))
                                { vProvinceDO.RegionID = Convert.ToString(dr["REGIONID"]); }


                                if (!Convert.IsDBNull(dr["PRINCENAME"]))
                                { vProvinceDO.PrinceName = Utils.ToUTF8String((byte[])dr["PRINCENAME"]); }

                                if (!Convert.IsDBNull(dr["LOTTERYNAME"]))
                                { vProvinceDO.LotteryName = Utils.ToUTF8String((byte[])dr["LOTTERYNAME"]); }

                                vProvinceDOCollection.Add(vProvinceDO);
                                dr = null;
                                vProvinceDO = null;
                            }

                            dt.Dispose();
                            dt = null;
                        }

                    }
                    return vProvinceDOCollection;
                }
            }
        }

        public DataTable SelectAll_page(int pageIndex, int pageSize, ref int rowCount)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "Province_PKG.GerAllProvincePage";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
            oraParam.Value = pageIndex;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
            oraParam.Value = pageSize;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];

                    if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                    { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return dt;
        }

        public DataTable SelectAll_page_idvung(string id_vung, int pageIndex, int pageSize, ref int rowCount)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "Province_PKG.GerAllProvincePage_Region";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_REGION_ID", OracleDbType.Varchar2);
            oraParam.Value = id_vung;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
            oraParam.Value = pageIndex;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
            oraParam.Value = pageSize;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];

                    if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                    { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return dt;
        }

        public DataTable SelectAll_dropdownlist()
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "Province_PKG.GerAllProvinceNonePage";
            OracleParameter oraParam = null;


            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);


            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];

                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return dt;
        }
    }
}
